﻿Imports System.Data
Imports System.Data.SqlClient

Public Class ContasReceberDAL

    Function LocalizaAbertos(ByVal dataInicio As Date, ByVal datafinal As Date) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As New DataTable
        Try
            cmd.CommandText = "SELECT dbo.agendamento_carga.age_codigo AS REGISTRO, dbo.pessoa.pes_codigo AS CLI_CODIGO, dbo.pessoa.pes_nome AS CLI_NOME, SUM(dbo.cargas.car_valor) " &
                                  "AS VALOR_TOTAL, dbo.agendamento_carga.age_data AS DATA " &
                                  "FROM dbo.agendamento_carga INNER JOIN " &
                                  "dbo.cargas_do_agendamento ON dbo.agendamento_carga.age_codigo = dbo.cargas_do_agendamento.age_codigo INNER JOIN " &
                                  "dbo.cargas ON dbo.cargas_do_agendamento.car_codigo = dbo.cargas.car_codigo INNER JOIN " &
                                  "dbo.clientes_do_agendamento ON dbo.agendamento_carga.age_codigo = dbo.clientes_do_agendamento.age_codigo INNER JOIN " &
                                  "dbo.pessoa ON dbo.clientes_do_agendamento.pes_codigo = dbo.pessoa.pes_codigo " &
                                 "WHERE (dbo.clientes_do_agendamento.pes_tipo_dr = 'D') and dbo.agendamento_carga.age_codigo not in (select age_codigo from contas_receber) and dbo.agendamento_carga.age_data >= @dataInicio and dbo.agendamento_carga.age_data <= @datafinal " &
                                 "GROUP BY dbo.agendamento_carga.age_codigo, dbo.agendamento_carga.age_data, dbo.pessoa.pes_nome, dbo.pessoa.pes_codigo"
            cmd.Parameters.Add(New SqlParameter("@dataInicio", SqlDbType.Date)).Value = dataInicio
            cmd.Parameters.Add(New SqlParameter("@datafinal", SqlDbType.Date)).Value = datafinal
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function InserirContasAreceber(ByVal pes_codigo As Integer, ByVal cr_formaPgto As String, ByVal age_codigo As Integer, ByVal cr_valorTotal As Decimal, ByVal cr_dataLancamento As Date, ByVal pes_nome As String)
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim id As Integer
        Try
            cmd.CommandText = "INSERT INTO contas_receber([pes_codigo],[cr_formaPgto],[age_codigo],[cr_valorTotal],[cr_dataLancamento],[pes_nome])" &
                                    "VALUES(@pes_codigo,@cr_formaPgto,@age_codigo,@cr_valorTotal,@cr_dataLancamento,@pes_nome)"
            cmd.Parameters.Add(New SqlParameter("@pes_codigo", SqlDbType.Int)).Value = pes_codigo
            cmd.Parameters.Add(New SqlParameter("@cr_formaPgto", SqlDbType.VarChar)).Value = cr_formaPgto
            cmd.Parameters.Add(New SqlParameter("@age_codigo", SqlDbType.Int)).Value = age_codigo
            cmd.Parameters.Add(New SqlParameter("@cr_valorTotal", SqlDbType.Money)).Value = cr_valorTotal
            cmd.Parameters.Add(New SqlParameter("@cr_dataLancamento", SqlDbType.Date)).Value = cr_dataLancamento
            cmd.Parameters.Add(New SqlParameter("@pes_nome", SqlDbType.VarChar)).Value = pes_nome
            id = objD.executacomandoAI(cmd, "contas_receber")
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return id
    End Function


    Function LozalizaLancados(ByVal dataInicio As Date, ByVal datafinal As Date) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As New DataTable
        Try
            cmd.CommandText = "select cr.cr_codigo REG, cr.age_codigo AGEN, cr.pes_codigo CLICOD, cr.pes_nome CLINOME, SUM(pr.pr_valor) TOTAL, cr.cr_dataLancamento DATA, cr.cr_formaPgto FORMAPGTO " &
                               "from contas_receber cr , parcelas_receber pr " &
                               "where cr.cr_codigo in (select cr_codigo from parcelas_receber where pr_status_pg = 'B') " &
                               "and pr.pr_status_pg = 'B' and cr.cr_formaPgto = 'DUPLICATA' and cr.cr_dataLancamento >= @dataInicio and cr.cr_dataLancamento <= @datafinal and cr.cr_codigo = pr.cr_codigo " &
                               "group by cr.cr_codigo,cr.age_codigo, cr.pes_codigo ,cr.pes_nome, pr.pr_valor, cr.cr_dataLancamento, cr.cr_formaPgto " &
                               " union " &
                               "select cr_codigo REG, age_codigo AGEN, pes_codigo CLICOD,pes_nome CLINOME,cr_valorTotal TOTAL,cr_dataLancamento DATA, cr_formaPgto FORMAPGTO from contas_receber " &
                               "where cr_formaPgto <> 'DUPLICATA' and cr_dataLancamento >= @dataInicio and cr_dataLancamento <= @datafinal"
            cmd.Parameters.Add(New SqlParameter("@dataInicio", SqlDbType.Date)).Value = dataInicio
            cmd.Parameters.Add(New SqlParameter("@datafinal", SqlDbType.Date)).Value = datafinal
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function


    Sub InserirParcelas(cr_codigo As Integer, pr_valor As Decimal, pr_d_vencimento As Date, pr_status_pg As Char, pr_parcela As Integer)
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim id As Integer
        Try
            cmd.CommandText = "INSERT INTO parcelas_receber([cr_codigo],[pr_valor],[pr_d_vencimento],[pr_status_pg],[pr_parcela]) " &
                                   "VALUES(@cr_codigo, @pr_valor, @pr_d_vencimento,@pr_status_pg,@pr_parcela)"
            cmd.Parameters.Add(New SqlParameter("@cr_codigo", SqlDbType.Int)).Value = cr_codigo
            cmd.Parameters.Add(New SqlParameter("@pr_valor", SqlDbType.Money)).Value = pr_valor
            cmd.Parameters.Add(New SqlParameter("@pr_d_vencimento", SqlDbType.Date)).Value = pr_d_vencimento
            cmd.Parameters.Add(New SqlParameter("@pr_status_pg", SqlDbType.Char)).Value = pr_status_pg
            cmd.Parameters.Add(New SqlParameter("@pr_parcela", SqlDbType.Int)).Value = pr_parcela
            id = objD.executacomandoAI(cmd, "parcelas_receber")
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub

    Function LocalizaParcelados(ByVal dataInicio As Date, ByVal datafinal As Date) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As New DataTable
        Try
            cmd.CommandText = "SELECT cr_codigo REG, age_codigo AGEN, pes_codigo CLICOD, pes_nome CLINOME, cr_valorTotal VALTOTAL, cr_dataLancamento DATA, cr_formaPgto FORMAPGTO " &
                                "FROM dbo.contas_receber " &
                                "WHERE cr_dataLancamento >= @dataInicio and cr_dataLancamento <= @datafinal and cr_codigo in (select cr_codigo from parcelas_receber)"
            cmd.Parameters.Add(New SqlParameter("@dataInicio", SqlDbType.Date)).Value = dataInicio
            cmd.Parameters.Add(New SqlParameter("@datafinal", SqlDbType.Date)).Value = datafinal
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function localizaParcelasAbertas(cr_codigo As Integer) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As New DataTable
        Try
            cmd.CommandText = "SELECT pr_codigo R, pr_parcela NUM , pr_d_vencimento VENC, pr_valor VALOR " &
                                "FROM dbo.parcelas_receber " &
                                "WHERE cr_codigo = @cr_codigo and pr_status_pg = 'A' "
            cmd.Parameters.Add(New SqlParameter("@cr_codigo", SqlDbType.Int)).Value = cr_codigo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function localizaParcelasBaixados(cr_codigo As Integer) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As New DataTable
        Try
            cmd.CommandText = "SELECT pr_parcela NUM , pr_d_vencimento VENCIMENTO, pr_valor VALOR " &
                                "FROM dbo.parcelas_receber " &
                                "WHERE cr_codigo = @cr_codigo and pr_status_pg = 'B' "
            cmd.Parameters.Add(New SqlParameter("@cr_codigo", SqlDbType.Int)).Value = cr_codigo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Sub BaixaParcelas(ByVal pr_codigo As Integer)
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Try
            cmd.CommandText = "UPDATE parcelas_receber set pr_status_pg = 'B' where pr_codigo = @pr_codigo"
            cmd.Parameters.Add(New SqlParameter("@pr_codigo", SqlDbType.Int)).Value = pr_codigo
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub
End Class
